﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Text;

namespace RESP_IIWaterAssessment
{
    public struct Rpoints
    {
        public int top_x,
                   top_y,
                   bottom_x,
                   bottom_y
                   ;
        public Rpoints(int p1, int p2, int p3, int p4)
        {
            top_x = p1;
            top_y = p2;
            bottom_x = p3;
            bottom_y = p4;
        }
    }
    public struct sPoint
    {
       public int x,
                  y
                  ;
    }
    public static class MakeExcel
    {
        public static void CopyTemplate(Worksheet shSource,Range s_range, Rpoints rpnts)
        {
            s_range.Copy(shSource.Cells[rpnts.top_x, rpnts.top_y]);
        }
        public static Rpoints AddCoord(Rpoints rpnts, sPoint spn)
        {
            rpnts.top_x = rpnts.top_x + spn.x;
            rpnts.top_y = rpnts.top_y + spn.y;
            rpnts.bottom_x = rpnts.bottom_x + spn.y;
            rpnts.bottom_y = rpnts.bottom_y + spn.y;
            return rpnts;
        }
        public static Range GetRange(Worksheet shSource, Rpoints rpnts)
        {
            return (Range)shSource.get_Range(shSource.Cells[rpnts.top_x, rpnts.top_y],
                                             shSource.Cells[rpnts.bottom_x, rpnts.bottom_y]);
        }
        public static void addBorder(Range tmpRange)
        {
            //Range tmpRange = excelworksheet.get_Range(p, p);
            tmpRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        }
        public static int FillRange(Worksheet excelworksheet, DataGridView dgvReport, int head_size, int top,
                                     int left, int beg_row,ref Rpoints rnpts, sPoint spn,int col)
        {
            Range tmprange;
            int i = beg_row;
            while (i < dgvReport.Rows.Count)
            {
                if (dgvReport[0, i].FormattedValue == "Итого")
                {
                    excelworksheet.Cells[i - beg_row + top, 3] = dgvReport[0, i].FormattedValue;
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top, 3],
                                                        excelworksheet.Cells[i - beg_row + top, 3]);
                    addBorder(tmprange);
                    for (int j = col; j < dgvReport.Columns.Count; j++)
                    {
                        excelworksheet.Cells[i - beg_row + top, dgvReport.Columns[j].DisplayIndex + left] = dgvReport[j, i].FormattedValue;
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top, dgvReport.Columns[j].DisplayIndex + left],
                                                                  excelworksheet.Cells[i - beg_row + top, dgvReport.Columns[j].DisplayIndex + left]);
                        addBorder(tmprange);
                    }
                    excelworksheet.Cells[i - beg_row + top, 2] = i + 1;
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top, 2],
                                                        excelworksheet.Cells[i - beg_row + top, 2]);
                    addBorder(tmprange);
                    rnpts=AddCoord(rnpts, spn);
                    beg_row = i + 1;
                    return i+1;
                }
                excelworksheet.Cells[i-beg_row + top, 3] = dgvReport[0, i].FormattedValue;
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top, 3],
                                                    excelworksheet.Cells[i - beg_row + top, 3]);
                addBorder(tmprange);
                for (int j = col; j < dgvReport.Columns.Count; j++)
                {
                    excelworksheet.Cells[i - beg_row + top, dgvReport.Columns[j].DisplayIndex + left] = dgvReport[j, i].FormattedValue;
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top, dgvReport.Columns[j].DisplayIndex + left],
                                                              excelworksheet.Cells[i - beg_row + top, dgvReport.Columns[j].DisplayIndex + left]);
                    addBorder(tmprange);
                }
                excelworksheet.Cells[i - beg_row + top, 2] = i + 1;
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top, 2],
                                                     excelworksheet.Cells[i - beg_row + top, 2]);
                addBorder(tmprange);
                rnpts = AddCoord(rnpts, spn);
                i++;
            }
            return -1;
        }
        public static int FillRange_seed(Worksheet excelworksheet, DataGridView dgvReport, int head_size, int top,
                                     int left, int beg_row, ref Rpoints rnpts, sPoint spn, int col)
        {
            Range tmprange;
            int i = beg_row;
            float s = 0;
            while (i < dgvReport.Rows.Count)
            {
                if (dgvReport[0, i].FormattedValue == "Итого")
                {
                    excelworksheet.Cells[i - beg_row + top+1, 3] = dgvReport[0, i].FormattedValue;
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top+1, 3],
                                                        excelworksheet.Cells[i - beg_row + top+1, 3]);
                    addBorder(tmprange);
                    for (int j = col; j < dgvReport.Columns.Count-1; j++)
                    {
                        if (j == dgvReport.Columns.Count - 2)
                        {
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1]);
                            tmprange.NumberFormat = "0,00";
                            //excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1] = dgvReport[j, i].FormattedValue;
                            tmprange.Font.Size = 8;
                            excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1] = s;
                            tmprange.HorizontalAlignment = 7;
                            addBorder(tmprange);
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j-1].DisplayIndex + left]);
                            tmprange.NumberFormat = "0,00";
                            tmprange.HorizontalAlignment = 7; 
                            excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j-1].DisplayIndex + left] = dgvReport[j-1, i].FormattedValue;
                            addBorder(tmprange);
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left+1],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left+1]);
                            addBorder(tmprange);
                        }
                        if (j != dgvReport.Columns.Count - 2)
                        {
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left]);
                            excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left] = dgvReport[j, i].FormattedValue;
                        }
                        addBorder(tmprange);
                    }
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top+1, 2],
                                                        excelworksheet.Cells[i - beg_row + top+1, 2]);
                    tmprange.HorizontalAlignment = 7;
                    excelworksheet.Cells[i - beg_row + top + 1, 2] = i + 1;
                    addBorder(tmprange);
                    rnpts = AddCoord(rnpts, spn);
                    beg_row = i + 1;
                    return i + 1;
                }
                excelworksheet.Cells[i - beg_row + top+1, 3] = dgvReport[0, i].FormattedValue;
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top+1, 3],
                                                    excelworksheet.Cells[i - beg_row + top+1, 3]);
                addBorder(tmprange);
                float v1;
                float v2;
                for (int j = col; j < dgvReport.Columns.Count-1; j++)
                {
                    if (j == dgvReport.Columns.Count - 2)
                    {
                        v1 = float.Parse(dgvReport[j, i].Value.ToString());
                        v2 = float.Parse(dgvReport[j - 1, i].Value.ToString());
                        s = s + v1*v2;
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1]);
                        tmprange.Font.Size = 8;
                        tmprange.HorizontalAlignment = 7;
                        tmprange.NumberFormat = "0,00";
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1] = v1 * v2;
                        addBorder(tmprange);
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left]);
                        tmprange.NumberFormat = "0,00";
                        tmprange.HorizontalAlignment = 7;
                        tmprange.Font.Size = 8;
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left] = dgvReport[j, i].FormattedValue;
                        addBorder(tmprange);
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-1],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-1]);
                        tmprange.NumberFormat = "0,00";
                        tmprange.HorizontalAlignment = 7;
                        tmprange.Font.Size = 8;
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-1] = v2;
                    }
                    if (j != dgvReport.Columns.Count - 2)
                    {
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left] = dgvReport[j, i].FormattedValue;
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left]);
                    }
                    addBorder(tmprange);
                }
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, 2],
                                                     excelworksheet.Cells[i - beg_row + top+1, 2]);
                tmprange.HorizontalAlignment = 7;
                excelworksheet.Cells[i - beg_row + top+1, 2] = i + 1;
                addBorder(tmprange);
                rnpts = AddCoord(rnpts, spn);
                i++;
            }
            return -1;
        }
        public static int FillRange_protection(Worksheet excelworksheet, DataGridView dgvReport, int head_size, int top,
                                     int left, int beg_row, ref Rpoints rnpts, sPoint spn, int col)
        {
            Range tmprange;
            int i = beg_row;
            float s = 0;
            while (i < dgvReport.Rows.Count)
            {
                if (dgvReport[0, i].FormattedValue == "Итого")
                {
                    excelworksheet.Cells[i - beg_row + top + 1, 3] = dgvReport[0, i].FormattedValue;
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, 3],
                                                        excelworksheet.Cells[i - beg_row + top + 1, 3]);
                    addBorder(tmprange);
                    for (int j = col; j < dgvReport.Columns.Count - 1; j++)
                    {
                        if (j == dgvReport.Columns.Count - 2)
                        {
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1 - col],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1 - col]);
                            tmprange.NumberFormat = "0,00";
                            //excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1] = dgvReport[j, i].FormattedValue;
                            tmprange.Font.Size = 8;
                            excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1 - col] = s;
                            tmprange.HorizontalAlignment = 7;
                            addBorder(tmprange);
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left - col],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left - col]);
                            tmprange.NumberFormat = "0,00";
                            tmprange.HorizontalAlignment = 7;
                            excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left - col] = dgvReport[j - 1, i].FormattedValue;
                            addBorder(tmprange);
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left + 1 - col],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j - 1].DisplayIndex + left + 1 - col]);
                            addBorder(tmprange);
                        }
                        if (j != dgvReport.Columns.Count - 2)
                        {
                            tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-col],
                                                                      excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-col]);
                            excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-col] = dgvReport[j, i].FormattedValue;
                        }
                        addBorder(tmprange);
                    }
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, 2],
                                                        excelworksheet.Cells[i - beg_row + top + 1, 2]);
                    tmprange.HorizontalAlignment = 7;
                    excelworksheet.Cells[i - beg_row + top + 1, 2] = i + 1;
                    addBorder(tmprange);
                    rnpts = AddCoord(rnpts, spn);
                    beg_row = i + 1;
                    return i + 1;
                }
                excelworksheet.Cells[i - beg_row + top + 1, 3] = dgvReport[0, i].FormattedValue;
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, 3],
                                                    excelworksheet.Cells[i - beg_row + top + 1, 3]);
                addBorder(tmprange);
                float v1;
                float v2;
                for (int j = col; j < dgvReport.Columns.Count - 1; j++)
                {
                    if (j == dgvReport.Columns.Count - 2)
                    {
                        v1 = float.Parse(dgvReport[j, i].Value.ToString());
                        v2 = float.Parse(dgvReport[j - 1, i].Value.ToString());
                        s = s + v1 * v2;
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1-col],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1 - col]);
                        tmprange.Font.Size = 8;
                        tmprange.HorizontalAlignment = 7;
                        tmprange.NumberFormat = "0,00";
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left + 1 - col] = v1 * v2;
                        addBorder(tmprange);
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - col],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - col]);
                        tmprange.NumberFormat = "0,00";
                        tmprange.HorizontalAlignment = 7;
                        tmprange.Font.Size = 8;
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - col] = dgvReport[j, i].FormattedValue;
                        addBorder(tmprange);
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - 1 - col],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - 1 - col]);
                        tmprange.NumberFormat = "0,00";
                        tmprange.HorizontalAlignment = 7;
                        tmprange.Font.Size = 8;
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - 1 - col] = v2;
                    }
                    if (j != dgvReport.Columns.Count - 2)
                    {
                        excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-col] = dgvReport[j, i].FormattedValue;
                        tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-col],
                                                                  excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left-col]);
                    }
                    addBorder(tmprange);
                }
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, 2],
                                                     excelworksheet.Cells[i - beg_row + top + 1, 2]);
                tmprange.HorizontalAlignment = 7;
                excelworksheet.Cells[i - beg_row + top + 1, 2] = i + 1;
                addBorder(tmprange);
                rnpts = AddCoord(rnpts, spn);
                i++;
            }
            return -1;
        }
        public static int FillRange_use(Worksheet excelworksheet, DataGridView dgvReport, int head_size, int top,
                                     int left, int beg_row, ref Rpoints rnpts, sPoint spn, int col)
        {
            Range tmprange;
            int i = beg_row;
            float s = 0;
            while (i < dgvReport.Rows.Count)
            {
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, 3],
                                                    excelworksheet.Cells[i - beg_row + top + 1, 3]);
                tmprange.HorizontalAlignment = 7;
                excelworksheet.Cells[i - beg_row + top + 1, 3] = dgvReport[0, i].FormattedValue;
                addBorder(tmprange);
                for (int j = col; j < dgvReport.Columns.Count; j++)
                {
                    tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - col],
                                                               excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - col]);
                    tmprange.NumberFormat = "0,00";
                    tmprange.HorizontalAlignment = 7;
                    excelworksheet.Cells[i - beg_row + top + 1, dgvReport.Columns[j].DisplayIndex + left - col] = dgvReport[j, i].FormattedValue;
                    addBorder(tmprange);
                }
                tmprange = excelworksheet.get_Range(excelworksheet.Cells[i - beg_row + top + 1, 2],
                                                     excelworksheet.Cells[i - beg_row + top + 1, 2]);
                tmprange.HorizontalAlignment = 7;
                excelworksheet.Cells[i - beg_row + top + 1, 2] = i + 1;
                addBorder(tmprange);
                rnpts = AddCoord(rnpts, spn);
                i++;
            }
            return -1;
        }
    }
}
